<?php
class ModelNewsletterTemplate extends Model {
    public function addTemplate($data) {
        $sql = "INSERT INTO " . DB_PREFIX . "newsletter_template SET " .
            " code = '" . $this->db->escape($data['code']) . "', " .
            " title = '" . $this->db->escape($data['title']) . "', " .
            " description = '" . $this->db->escape($data['description']) . "', " . 
            " product_title = '" . $this->db->escape($data['product_title']) . "', " .
            " product_limit = '" . (int)$data['product_limit'] . "', " .
			" footer_text = '" . $this->db->escape($data['footer_text']) . "', " .
            " status = '" . (int)$data['status'] . "', " .
            " date_added = NOW(), date_modified = NOW(), " .
			" layout = '" . $this->db->escape($data['template_layout']) . "', " .
			" simple_template = '" . (int)$data['simple_template'] . "'"; 
        $this->db->query($sql);
		
		$template_id = $this->db->getLastId();         
        if (isset($data['product_featured'])) {
            foreach ($data['product_featured'] as $featured) {
                $sql = "INSERT INTO " . DB_PREFIX . "newsletter_template_product SET " .
                    " template_id = '" . (int)$template_id . "', " .
                    " product_id = '" . (int)$featured['product_id'] . "', " .
                    " product_name = '" . $this->db->escape($featured['name']) . "', " .
                    " price = '" . $this->db->escape($featured['price']) . "', " .
                    " discount = '" . $this->db->escape($featured['discount']) . "', " .
                    " image = '" . $this->db->escape($featured['image']) . "'";
                       
                $this->db->query($sql);
            }            
        }
    }
    
    public function updateTemplate($template_id, $data) {
        $sql = "UPDATE " . DB_PREFIX . "newsletter_template SET " .
            "   code = '" . $this->db->escape($data['code']) . "', " .
            "   title = '" . $this->db->escape($data['title']) . "', " .
            "   description = '" . $this->db->escape($data['description']) . "', " . 
            "   product_title = '" . $this->db->escape($data['product_title']) . "', " .
            "   product_limit = '" . (int)$data['product_limit'] . "', " .
			" 	footer_text = '" . $this->db->escape($data['footer_text']) . "', " .
            "   status = '" . (int)$data['status'] . "', " .
            "   date_modified = NOW(), " .
			" 	layout = '" . $this->db->escape($data['template_layout']) . "', " .
			" 	simple_template = '" . (int)$data['simple_template'] . "' " . 
            " WHERE template_id = '" . (int)$template_id . "'"; 
        $this->db->query($sql);
        
        $this->db->query("DELETE FROM " . DB_PREFIX . "newsletter_template_product WHERE template_id = '" . (int)$template_id . "'");          
        if (isset($data['product_featured'])) {
            foreach ($data['product_featured'] as $featured) {
                $sql = "INSERT INTO " . DB_PREFIX . "newsletter_template_product SET " .
                    " template_id = '" . (int)$template_id . "', " .
                    " product_id = '" . (int)$featured['product_id'] . "', " .
                    " product_name = '" . $this->db->escape($featured['name']) . "', " .
                    " price = '" . $this->db->escape($featured['price']) . "', " .
                    " discount = '" . $this->db->escape($featured['discount']) . "', " .
                    " image = '" . $this->db->escape($featured['image']) . "'";
                       
                $this->db->query($sql);
            }            
        }
    }
    
	public function deleteTemplate($template_id){
        $this->db->query("DELETE FROM " . DB_PREFIX . "newsletter_template WHERE template_id = '" . (int)$template_id . "'");
    }
	
    public function getProducts($template_id) {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "newsletter_template_product WHERE template_id = '" . (int)$template_id . "' ORDER BY product_template_id");
        
        $featured = array();         
        foreach ($query->rows as $product) {
            $featured[] = array(
                'product_id'    => $product['product_id'],
                'name'          => $product['product_name'],
                'price'         => $product['price'],
                'discount'      => $product['discount'],
                'image'         => $product['image'],
            );
        }
        return $featured;
    }
    
    public function getTemplates($data = array(), $simple = 0) {
		$sql = "SELECT * FROM " . DB_PREFIX . "newsletter_template WHERE template_id > 0 AND IFNULL(simple_template, 0) = '" . $simple . "'";
        
        if (isset($data['filter_code']) && !is_null($data['filter_code'])) {
			$sql .= " AND LCASE(code) LIKE '%" . $this->db->escape(strtolower($data['filter_code'])) . "%'";
		}
           
        if (isset($data['filter_title']) && !is_null($data['filter_title'])) {
            $sql .= " AND LCASE(title) LIKE '%" . $this->db->escape(strtolower($data['filter_title'])) . "%'";
        }
            
        if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
            $sql .= " AND status = '" . (int)$data['filter_status'] . "'";
        }

        $sort_data = array(
            'code',
            'title',
            'status',            
        );	
			
        if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            $sql .= " ORDER BY " . $data['sort'];	
        }
        else {
            $sql .= " ORDER BY code";	
        }
			
        if (isset($data['order']) && ($data['order'] == 'DESC')) {
            $sql .= " DESC";
        }
        else {
            $sql .= " ASC";
        }
		
        if (isset($data['start']) || isset($data['limit'])) {
            if ($data['start'] < 0) {
                $data['start'] = 0;
            }				

            if ($data['limit'] < 1) {
                $data['limit'] = 20;
            }	
        
            $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
        }
        
        $query = $this->db->query($sql);
		
		return $query->rows;
    }
        
    public function getTotalTemplates($data = array(), $simple = 0) {
		$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "newsletter_template WHERE template_id > 0 AND IFNULL(simple_template, 0) = '" . $simple . "'";
		
        if (isset($data['filter_code']) && !is_null($data['filter_code'])) {
			$sql .= " AND LCASE(code) LIKE '%" . $this->db->escape(strtolower($data['filter_code'])) . "%'";
		}
           
        if (isset($data['filter_title']) && !is_null($data['filter_title'])) {
            $sql .= " AND LCASE(title) LIKE '%" . $this->db->escape(strtolower($data['filter_title'])) . "%'";
        }
            
        if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
            $sql .= " AND status = '" . (int)$data['filter_status'] . "'";
        }
        
		$query = $this->db->query($sql);
		
		return $query->row['total'];
	}
    
    public function getTemplate($template_id) {
		$sql = "SELECT * FROM " . DB_PREFIX . "newsletter_template WHERE template_id = '" . (int)$template_id . "'";
        $query = $this->db->query($sql);
		
		return $query->row;
    }
    
    public function getAllTemplates() {
		$sql = "SELECT * FROM " . DB_PREFIX . "newsletter_template";
        
        $query = $this->db->query($sql);
		
		return $query->rows;
    }
    
	public function getEnabledTemplates() {
		$sql = "SELECT * FROM " . DB_PREFIX . "newsletter_template WHERE status = '1' ORDER BY `date_added` DESC";
        
        $query = $this->db->query($sql);
		
		return $query->rows;
    }
	
    public function getCategories($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'");
		
		return $query->rows;
	}
    
    public function getParentCategory($category_id) {
		$query = $this->db->query("SELECT parent_id FROM " . DB_PREFIX . "category WHERE category_id = '" . (int)$category_id . "'"); 
		
		return !empty($query->row) ? $query->row['parent_id'] : 0;
	}
    
    public function getCategoriesPath($product_id) { 
		$categories = $this->getCategories($product_id);
		reset($categories);
		$cat = current($categories);
		$current_cat = $cat['category_id'];  
		$categories = array();    		  
		 				
		while($current_cat > 0){
			$categories[] = $current_cat;			
			$current_cat = $this->getParentCategory($current_cat);				
		}
		
		$categories = array_reverse($categories);
		 
		return implode('_', $categories);
	}
    
    public function getDiscount($product_id) { 
        $status = TRUE;          
        $sql = "SELECT d.* FROM " . DB_PREFIX . "discount d " .
            " LEFT JOIN " . DB_PREFIX . "discount_description dc ON (d.discount_id = dc.discount_id) " .
            " LEFT JOIN " . DB_PREFIX . "discount_product dp ON (d.discount_id = dp.discount_id) " .
            " WHERE dc.language_id = '" . (int)$this->config->get('config_language_id') . "' AND dp.product_id = '" . (int)$product_id . "' AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) AND d.status = '1' " . 
            " ORDER BY d.priority ASC LIMIT 0,1";
        
        $discount_query = $this->db->query($sql);        
        $price = $this->getProductPrice($product_id);
            
        if ($discount_query->num_rows) {
            if($discount_query->row['type'] = 'P'){                  
                $discount = $price - $price * $discount_query->row['discount'] / 100;
            }
            else{
                $discount = $price - $discount_query->row['discount'];
            }                      
            
            return $discount;
        
            /*                     
            $result =  array(
                'discount'  => $discount,
                'percent'   => $discount_query->row['type'] == 'P',
                'value'     => $discount_query->row['discount'],
            );
            
            return $result;
            */
        }
        
        return 0;
    }
    
    private function getProductPrice($product_id) {
        $query = $this->db->query("SELECT price FROM " . DB_PREFIX . "product WHERE product_id = '" . (int)$product_id . "' AND date_available <= NOW() AND status = '1'");
    
        return $query->num_rows ? $query->row['price'] : 0;
    }
    
    public function getProductDiscounts($product_id) {
		$customer_group_id = $this->config->get('config_customer_group_id');        
		
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$customer_group_id . "' AND quantity > 0 AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY quantity ASC, priority ASC, price ASC");

		return $query->rows;		
	}
}
?>